﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VehicleConfigurator.NetUtils.Database;
using VehicleConfigurator.Database;
using VehicleConfigurator.Model;
using System.Collections;
using VehicleConfigurator.NetUtils;
using VehicleConfigurator.FormDetails;
using System.IO;
using VehicleConfigurator.NetUtils.Settings;
using VehicleConfigurator.NetUtils.Model;
using System.Text.RegularExpressions;
using System.Globalization;

namespace VehicleConfigurator.TabControls
{
    public partial class OptionPriceListControl : UserControl
    {
        List<String> idDelete = new List<string>();
        private string tax = "";
        public OptionPriceListControl()
        {
            InitializeComponent();
        }
        private object _1INSTANCE;

        private void optionPriceListControl_Load(object sender, EventArgs e)
        {
        }

        public void GetVatFromCountry(string countryoid)
        {
            tax = DBAccess.GetTaxFromCountry(countryoid);

        }

        private void loadComboColumn()
        {
            if (this.cboMakes.SelectedValue != null)
            {
                var makeOid = this.cboMakes.SelectedValue.ToString();
                var og = DBAccess.GetOptionGroup(makeOid);
                loadComboColumnOptionGroup(og);
            }
            //if (this.cboModel.SelectedValue != null)
            //{
            //    var modelOid = cboModel.SelectedValue.ToString();
            //    var me = DBAccess.GetModelEdition(modelOid);
            //    loadComboColumnModelEdtion(me);
            //}
        }

        private void loadComboColumnOptionGroup(DataTable optionGroup)
        {
            var comboBox = this.gvOptionPricelistNamed.Columns[FieldNames.OPTION_GROUP_PRICELIST] as DataGridViewComboBoxColumn;
            comboBox.DataSource = optionGroup;
            comboBox.ValueMember = FieldNames.OID;
            comboBox.DisplayMember = FieldNames.NAME;

        }

        //private void loadComboColumnModelEdtion(DataTable modelEdition)
        //{
        //    var comboBox = this.gvOptionPricelistNamed.Columns[FieldNames.OID_MODEL_EDITION] as DataGridViewComboBoxColumn;
        //    comboBox.DataSource = modelEdition;
        //    comboBox.ValueMember = FieldNames.OID;
        //    comboBox.DisplayMember = FieldNames.NAME;
        //}

        public void LoadDataComponent()
        {
            if (_1INSTANCE == null)
            {
                _1INSTANCE = this;
            }
            else
            {
                return;
            }
            try
            {
                //var optionPricelist = DBAccess.GetOptionPricelist();
                var optionPricelistDate = DBAccess.GetOptionPricelistDate();
                // Make
                this.cboMakes.DataSource = DBAccess.GetMakes();
                this.cboMakes.ValueMember = FieldNames.OID;
                this.cboMakes.DisplayMember = FieldNames.NAME;
                // Country
                DataTable countryData = DBAccess.GetCountry();
                this.cboCountry.DataSource = countryData;
                this.cboCountry.ValueMember = FieldNames.OID;
                this.cboCountry.DisplayMember = FieldNames.NAME;
                Utils.SetDefault(countryData, this.cboCountry);
                // Currency
                var currencyData = DBAccess.GetCurrency();
                this.cboCurrency.DataSource = currencyData;
                this.cboCurrency.ValueMember = FieldNames.OID;
                this.cboCurrency.DisplayMember = FieldNames.NAME;
                Utils.SetDefault(currencyData, this.cboCurrency);
                // PriceListDate
                var dateOnly = optionPricelistDate.AsEnumerable().Select(row => row[FieldNames.PRICE_LIST_DATE]).Distinct().ToList();
                if (!dateOnly.Contains(DateTime.Today))
                {
                    dateOnly.Insert(0, DateTime.Today);
                }
                dateOnly.Sort();
                dateOnly.Reverse();
                this.cboPricelistDateOptionPricelist.DataSource = dateOnly;
                this.cboPricelistDateOptionPricelist.FormatString = AppConfig.Constants.DEFAULT_DATE_FORMAT;

                // Language
                var languageData = DBAccess.GetLanguage();
                this.cbLanguageOptionPricelist.DataSource = languageData;
                this.cbLanguageOptionPricelist.ValueMember = FieldNames.OID;
                this.cbLanguageOptionPricelist.DisplayMember = FieldNames.NAME;
                Utils.SetDefault(languageData, this.cbLanguageOptionPricelist);


                // Load datagridview
                // this.gvOptionPricelistNamed.DataSource = null;

                // Do not load
                this.loadComboColumn();
                this.gvOptionPricelistNamed.DataSource = DBAccess.GetModelPricelistEmpty();
                this.Reorder(new string[0]);

                this.btnSearchOptionPricelist_Click(this, null);

                // Set datasource gridview
                //var comboBox = this.gvOptionPricelistNamed.Columns["OptionGroupPriceList"] as DataGridViewComboBoxColumn;
                ////var ds = (DataTable)this.gvOptionPricelist.DataSource;
                //comboBox.DataSource = DBAccess.GetOptionGroup();
                //comboBox.ValueMember = FieldNames.OID;
                //comboBox.DisplayMember = FieldNames.NAME;

                ////modelyear
                //DataGridViewComboBoxColumn comboColYear = (DataGridViewComboBoxColumn)this.gvOptionPricelist.Columns["ModelYearMakeOID"];
                //comboColYear.DataSource = ds;
                //comboColYear.ValueMember = "OID";
                //comboColYear.DisplayMember = "Name";

                ////OptionGroup
                //DataGridViewComboBoxColumn comboColOption = (DataGridViewComboBoxColumn)this.gvOptionPricelist.Columns["OptionGroupMakeOID"];
                //comboColOption.DataSource = ds;
                //comboColOption.ValueMember = "OID";
                //comboColOption.DisplayMember = "Name";

                ////modelEdition
                //clsDb db = new clsDb();
                //DataTable dt = new DataTable();
                //string sql = "select * from Model";
                //dt = db.GetDataTable(sql);
                //if (dt != null)
                //{
                //    DataGridViewComboBoxColumn comboColEdition = new DataGridViewComboBoxColumn();
                //    comboColEdition = (DataGridViewComboBoxColumn)this.gvOptionPricelist.Columns["ModelEditionModelOID"];
                //    comboColEdition.DataSource = dt;
                //    comboColEdition.ValueMember = "OID";
                //    comboColEdition.DisplayMember = "Name";
                //}

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private OptionPrice parseFromDataRow(DataRow dataRow)
        {
            var option = new Option();
            var price = new OptionPrice();
            var pricelistDate = cboPricelistDateOptionPricelist.SelectedIndex == -1 ? DateTime.Today : ((DateTime)cboPricelistDateOptionPricelist.SelectedValue);
            price.OptionOID = string.IsNullOrEmpty(dataRow[FieldNames.OID_OPTION].ToString()) ? -1 : (int)dataRow[FieldNames.OID_OPTION];
            //price.ModelEditionOID = (int)dataRow[FieldNames.OID_MODEL_EDITION];
            price.PriceListDate = dataRow[FieldNames.PRICE_LIST_DATE] == null || dataRow[FieldNames.PRICE_LIST_DATE] is System.DBNull ? pricelistDate : (DateTime)dataRow[FieldNames.PRICE_LIST_DATE];
            price.ModelYearOID = (int)dataRow[FieldNames.OID_MODEL_YEAR];
            price.CountryOID = (int)dataRow[FieldNames.OID_COUNTRY];
            price.CurrencyOID = (int)dataRow[FieldNames.OID_CURRENCY];

            option.OptionCode = dataRow[FieldNames.OPTION_CODE] as string;
            option.BaseName = dataRow[FieldNames.BASE_NAME] as string;
            option.Explanation = dataRow[FieldNames.EXPLANATION] as string;
            option.OID = dataRow[FieldNames.OID_OPTION] == null || dataRow[FieldNames.OID_OPTION] is System.DBNull ? -1 : (int)dataRow[FieldNames.OID_OPTION];
            option.GroupOID = (int)dataRow[FieldNames.OID_GROUP];

            price.Option = option;
            price.Description = string.Empty;
            price.DealerNetPrice = dataRow["DealerNetPrice"].ToString();
            price.DealerNetPriceVAT = dataRow["DealerNetPriceVAT"].ToString();
            price.RetailPrice = dataRow["RetailPrice"].ToString();
            price.RetailPriceVAT = dataRow["RetailPriceVAT"].ToString();
            price.TaxPercentage1 = dataRow["TaxPercentage1"].ToString();
            price.TaxPercentage2 = dataRow["TaxPercentage2"].ToString();
            price.TaxPercentage3 = dataRow["TaxPercentage3"].ToString();
            price.TaxAmount1 = dataRow["TaxAmount1"].ToString();
            price.TaxAmount2 = dataRow["TaxAmount2"].ToString();
            price.TaxAmount3 = dataRow["TaxAmount3"].ToString();
            return price;
        }

        public Hashtable getDataFromRow(DataRow dr)
        {
            Hashtable ht = new Hashtable();

            Option o = new Option();
            int oid = -1;
            bool tem = false;
            tem = int.TryParse(dr[FieldNames.OID].ToString(), out oid);
            if (tem)
                o.OID = oid;

            o.OptionCode = dr[FieldNames.OPTION_CODE].ToString();
            int val = -1;
            bool result = int.TryParse(dr[FieldNames.OID_GROUP].ToString(), out val);
            if (result)
                o.GroupOID = val;
            o.BaseName = dr[FieldNames.BASE_NAME].ToString();
            o.Explanation = dr[FieldNames.EXPLANATION].ToString();

            ht.Add("Option", o);

            int temp;
            OptionPrice op = new OptionPrice();
            if (int.TryParse(dr["optionpricelistOid"].ToString(), out temp))
            {
                op.OID = temp;
            }
            else
                op.OID = -1;

            op.OptionOID = oid;

            int temp2;
            if (int.TryParse(dr["modeleditionOID"].ToString(), out temp2))
            {
                op.ModelEditionOID = temp2;
            }
            else
                op.ModelEditionOID = -1;
            // op.ModelEditionOID = dr[FieldNames.OID_MODEL_EDITION] == null ? 1 : dr[FieldNames.OID_MODEL_EDITION];
            op.ModelYearOID = this.cboModelYear.SelectedIndex == -1 ? -1 : (int)this.cboModelYear.SelectedValue;
            op.OptionOID = o.OID;

            op.PriceListDate = dr[FieldNames.PRICE_LIST_DATE] == null ? DateTime.Today : (DateTime)dr[FieldNames.PRICE_LIST_DATE];
            op.CountryOID = this.cboCountry.SelectedIndex == -1 ? -1 : (int)this.cboCountry.SelectedValue;
            op.CurrencyOID = this.cboCurrency.SelectedIndex == -1 ? -1 : (int)this.cboCurrency.SelectedValue;

            op.TaxPercentage1 = dr["TaxPercentage1"].ToString();
            op.TaxPercentage2 = dr["TaxPercentage2"].ToString();
            op.TaxPercentage3 = dr["TaxPercentage3"].ToString();

            op.TaxAmount1 = dr["TaxAmount1"].ToString();
            op.TaxAmount2 = dr["TaxAmount2"].ToString();
            op.TaxAmount3 = dr["TaxAmount3"].ToString();

            op.DealerNetPrice = dr["DealerNetPrice"].ToString();
            op.RetailPrice = dr["RetailPrice"].ToString();
            op.DealerNetPriceVAT = dr["DealerNetPriceVAT"].ToString();
            op.RetailPriceVAT = dr["RetailPriceVAT"].ToString();
            //fix code
            op.Description = "";
            // op.Description = dr["Description"].ToString();

            ht.Add("OptionPrice", op);

            return ht;

            // o.GroupOID = dr[FieldNames.
        }


        public void SaveGrid(DataTable dt, bool isImport)
        {
            var listAdd = new List<OptionPrice>();
            var listDelete = new List<string>();
            var mapUpdate = new Hashtable();
            var me = this.gvOptionPricelistNamed.Columns["ModelEditionOID"].Tag as DataTable;
            var update = dt.GetChanges(DataRowState.Modified);
            // process dynamic edition
            if (update != null && update.Rows.Count > 0)
            {
                if (me != null && me.Rows.Count > 0)
                {
                    var meNames = me.AsEnumerable().Select(rw => rw[FieldNames.NAME]).Distinct().ToArray();
                    var meOids = me.AsEnumerable().Select(rw => rw[FieldNames.OID]).Distinct().ToArray();
                    foreach (var item in update.Rows)
                    {
                        var dataRow = item as DataRow;
                        var optionPrice = parseFromDataRow(dataRow);
                        foreach (var name in meNames)
                        {
                            var meName = name as string;
                            var tmpOptionOid = (int)dataRow["OptionOid"];
                            var tmpModelYearOid = (int)dataRow["ModelYearOID"];
                            var tmpCountryOid = (int)dataRow["CountryOID"];
                            var tmpCurrencyOid = (int)dataRow["CurrencyOID"];
                            var tmpPricelistDate = (DateTime)dataRow["PriceListDate"];
                            var tmpModelEditionOids = dataRow["ModelEditionOID"] as string;
                            var tmpOptionPricelistOids = dataRow["OptionPricelistOid"] as string;
                            var tmpDynamicEditionValue = dataRow[meName] as string;

                            var filter = string.Format("Name = '{0}'", meName);
                            var rows = me.Select(filter);
                            var meOid = rows[0][FieldNames.OID].ToString();
                            //var tmpModelEditionName = me.Select(string.Format("OID = '{0}'", tmpModelEditionOids))[0][FieldNames.NAME] as string;

                            var tmpOptionFilter = string.Format("OptionOid = '{0}' AND ModelEditionOID = '{0}'", tmpOptionOid, meOid);
                            var results = dt.Select(tmpOptionFilter);

                            if (AppConfig.Pricing.UNAVAILABLE.Equals(tmpDynamicEditionValue))
                            {
                                //item1.Delete();
                                var tokens = tmpOptionPricelistOids.Split(new[] { ',' });
                                foreach (var token in tokens)
                                {
                                    var tmpOid = token.Split(new[] { '|' });
                                    if (tmpOid.Length > 1 && tmpOid[1].Equals(meOid))
                                    {
                                        listDelete.Add(tmpOid[0]);
                                        //break;
                                    }
                                }
                            }
                            else if (AppConfig.Pricing.STANDARD.Equals(tmpDynamicEditionValue))
                            {
                                var tokens = tmpOptionPricelistOids.Split(new[] { ',' });
                                var optionPricelistOid = string.Empty;
                                foreach (var token in tokens)
                                {
                                    var tmpOid = token.Split(new[] { '|' });
                                    if (tmpOid.Length > 1 && tmpOid[1].Equals(meOid))
                                    {
                                        optionPricelistOid = tmpOid[0];
                                        //break;
                                        var updatePrice = optionPrice.Copy();

                                        // standard
                                        updatePrice.DealerNetPrice
                                            = updatePrice.DealerNetPriceVAT
                                            = updatePrice.RetailPrice
                                            = updatePrice.RetailPriceVAT
                                            = updatePrice.TaxAmount1
                                            = updatePrice.TaxAmount2
                                            = updatePrice.TaxAmount3
                                            = updatePrice.TaxPercentage1
                                            = updatePrice.TaxPercentage2
                                            = updatePrice.TaxPercentage3
                                            = "0";
                                        updatePrice.OID = int.Parse(optionPricelistOid);
                                        updatePrice.ModelEditionOID = int.Parse(meOid);
                                        mapUpdate[optionPricelistOid] = updatePrice;
                                    }
                                }
                                if (!string.IsNullOrEmpty(optionPricelistOid))
                                {
                                    // update price to zero.
                                    // Add new
                                    //var updatePrice = parseFromDataRow(dataRow);
                                    //var updatePrice = optionPrice.Copy();

                                    //// standard
                                    //updatePrice.DealerNetPrice
                                    //    = updatePrice.DealerNetPriceVAT
                                    //    = updatePrice.RetailPrice
                                    //    = updatePrice.RetailPriceVAT
                                    //    = updatePrice.TaxAmount1
                                    //    = updatePrice.TaxAmount2
                                    //    = updatePrice.TaxAmount3
                                    //    = updatePrice.TaxPercentage1
                                    //    = updatePrice.TaxPercentage2
                                    //    = updatePrice.TaxPercentage3
                                    //    = "0";
                                    //updatePrice.OID = int.Parse(optionPricelistOid);
                                    //updatePrice.ModelEditionOID = int.Parse(meOid);
                                    //mapUpdate[optionPricelistOid] = updatePrice;
                                }
                                else
                                {
                                    // Add new
                                    //var newPrice = parseFromDataRow(dataRow);
                                    var newPrice = optionPrice.Copy();
                                    newPrice.ModelEditionOID = int.Parse(meOid);
                                    // standard
                                    newPrice.DealerNetPrice
                                        = newPrice.DealerNetPriceVAT
                                        = newPrice.RetailPrice
                                        = newPrice.RetailPriceVAT
                                        = newPrice.TaxAmount1
                                        = newPrice.TaxAmount2
                                        = newPrice.TaxAmount3
                                        = newPrice.TaxPercentage1
                                        = newPrice.TaxPercentage2
                                        = newPrice.TaxPercentage3
                                        = "0";

                                    listAdd.Add(newPrice);
                                }
                            }
                            else if (AppConfig.Pricing.OPTIONAL.Equals(tmpDynamicEditionValue))
                            {

                                var tokens = tmpOptionPricelistOids.Split(new[] { ',' });
                                var optionPricelistOid = string.Empty;
                                foreach (var token in tokens)
                                {
                                    var tmpOid = token.Split(new[] { '|' });
                                    if (tmpOid.Length > 1 && tmpOid[1].Equals(meOid))
                                    {
                                        optionPricelistOid = tmpOid[0];
                                        // update price to zero.
                                        var updatePrice = optionPrice.Copy();
                                        updatePrice.OID = int.Parse(optionPricelistOid);
                                        updatePrice.ModelEditionOID = int.Parse(meOid);
                                        mapUpdate[optionPricelistOid] = updatePrice;
                                    }
                                }
                                if (!string.IsNullOrEmpty(optionPricelistOid))
                                {
                                    // update price to zero.
                                    //var updatePrice = parseFromDataRow(dataRow);
                                    //updatePrice.OID = int.Parse(optionPricelistOid);
                                    //updatePrice.ModelEditionOID = int.Parse(meOid);
                                    //mapUpdate[optionPricelistOid] = updatePrice;
                                }
                                else
                                {
                                    // Add new
                                    var newPrice = parseFromDataRow(dataRow);
                                    newPrice.ModelEditionOID = int.Parse(meOid);
                                    listAdd.Add(newPrice);
                                }

                            }

                        }
                    }
                }

                // end process dynamic edition
            }

            var added = dt.GetChanges(DataRowState.Added);
            if (added != null && added.Rows.Count > 0)
            {
                var meNames = me.AsEnumerable().Select(rw => rw[FieldNames.NAME]).Distinct().ToArray();
                var meOids = me.AsEnumerable().Select(rw => rw[FieldNames.OID]).Distinct().ToArray();

                foreach (var item in added.Rows)
                {
                    var dataRow = item as DataRow;
                    var optionPrice = parseFromDataRow(dataRow);
                    //var ht = getDataFromRow(dataRow);
                    //var option = ht["Option"] as Option;
                    //var optionPrice = ht["OptionPrice"] as OptionPrice;

                    foreach (var name in meNames)
                    {
                        var meName = name as string;
                        var filter = string.Format("Name = '{0}'", meName);
                        var rows = me.Select(filter);
                        var meOid = (int)rows[0][FieldNames.OID];
                        // Oid Key
                        //var tmpOptionOid = (int)dataRow["OptionOid"]; // check null ??
                        var tmpModelYearOid = (int)dataRow["ModelYearOID"];
                        var tmpCountryOid = (int)dataRow["CountryOID"];
                        var tmpCurrencyOid = (int)dataRow["CurrencyOID"];
                        var tmpPricelistDate = (DateTime)dataRow["PriceListDate"];
                        var tmpModelEditionOids = dataRow["ModelEditionOID"] as string;
                        //var tmpOptionPricelistOids = dataRow["OptionPricelistOid"] as string;
                        var tmpDynamicEditionValue = dataRow[meName] as string;
                        //// Calculate Price ?
                        //var tmpDealerNetPrice = optionPrice.DealerNetPrice;
                        //var tmpDealerNetPriceVAT = optionPrice.DealerNetPriceVAT;
                        //var tmpRetailPrice = optionPrice.RetailPrice;
                        //var tmpRetailPriceVAT = optionPrice.RetailPriceVAT;

                        // get all model owned this option
                        //var tmpOptionFilter = string.Format("OptionOid = '{0}' AND ModelYearOID = '{1}' AND CountryOID = '{2}' AND CurrencyOID = '{3}' AND PriceListDate = '{4}'"
                        //    , tmpOptionOid
                        //    , tmpModelYearOid
                        //    , tmpCountryOid
                        //    , tmpCurrencyOid
                        //    , tmpPricelistDate
                        //    );
                        var tmpOptionFilter = string.Format("OptionCode = '{0}' AND GroupOID = '{1}' AND CountryOID <> '{2}' AND CurrencyOID <> '{3}' AND ModelYearOID <> '{4}' AND PriceListDate <> #{5}# AND ModelEditionOID <> '{6}'"
                            , optionPrice.Option.OptionCode
                            , optionPrice.Option.GroupOID
                            , tmpCountryOid, tmpCurrencyOid
                            , tmpModelYearOid, tmpPricelistDate.ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT)
                            , tmpModelEditionOids);
                        //tmpOptionFilter = "OptionCode = 'optioncode' AND GroupOID = '14' AND CountryOID <> '45' AND CurrencyOID <> '12' AND ModelYearOID <> '15' AND PriceListDate <> 04.04.2015 0.00.00 AND ModelEditionOID <> ''";
                        var results = dt.Select(tmpOptionFilter);


                        //var tmpTotal = double.Parse(tmpDealerNetPrice) + double.Parse(tmpDealerNetPriceVAT) + double.Parse(tmpRetailPrice) + double.Parse(tmpRetailPriceVAT);
                        //var tmpOptionFilter = string.Format("OptionOid = '{0}' AND ModelEditionOID <> '{1}'", tmpOptionOid, tmpModelEditionOids);
                        //var optionPricelist = dt.Select(tmpOptionFilter);

                        if (results.Length > 0)
                        {
                            // get 1 row
                            var optionPricelist = results[0];
                            var updatePrice = parseFromDataRow(optionPricelist);
                            if (AppConfig.Pricing.STANDARD.Equals(tmpDynamicEditionValue))
                            {
                                // standard
                                updatePrice.DealerNetPrice
                                    = optionPrice.DealerNetPriceVAT
                                    = optionPrice.RetailPrice
                                    = optionPrice.RetailPriceVAT
                                    = optionPrice.TaxAmount1
                                    = optionPrice.TaxAmount2
                                    = optionPrice.TaxAmount3
                                    = optionPrice.TaxPercentage1
                                    = optionPrice.TaxPercentage2
                                    = optionPrice.TaxPercentage3
                                    = "0";
                                var key = updatePrice.Option.GroupOID + updatePrice.Option.OptionCode + meOid;
                                mapUpdate[key] = updatePrice;
                            }
                            else if (AppConfig.Pricing.OPTIONAL.Equals(tmpDynamicEditionValue))
                            {
                                // Update Price
                                updatePrice.DealerNetPrice = optionPrice.DealerNetPrice;
                                updatePrice.DealerNetPriceVAT = optionPrice.DealerNetPriceVAT;
                                updatePrice.RetailPrice = optionPrice.RetailPrice;
                                updatePrice.RetailPriceVAT = optionPrice.RetailPriceVAT;
                                updatePrice.TaxAmount1 = optionPrice.TaxAmount1;
                                updatePrice.TaxAmount2 = optionPrice.TaxAmount2;
                                updatePrice.TaxAmount3 = optionPrice.TaxAmount3;
                                updatePrice.TaxPercentage1 = optionPrice.TaxPercentage1;
                                updatePrice.TaxPercentage2 = optionPrice.TaxPercentage2;
                                updatePrice.TaxPercentage3 = optionPrice.TaxPercentage3;
                                var key = updatePrice.Option.GroupOID + updatePrice.Option.OptionCode + meOid;
                                mapUpdate[key] = updatePrice;
                            }
                        }
                        else
                        {

                            if (AppConfig.Pricing.STANDARD.Equals(tmpDynamicEditionValue))
                            {
                                // Update zero
                                var newPrice = optionPrice.Copy();
                                newPrice.ModelEditionOID = meOid;
                                // standard
                                newPrice.DealerNetPrice
                                    = newPrice.DealerNetPriceVAT
                                    = newPrice.RetailPrice
                                    = newPrice.RetailPriceVAT
                                    = newPrice.TaxAmount1
                                    = newPrice.TaxAmount2
                                    = newPrice.TaxAmount3
                                    = newPrice.TaxPercentage1
                                    = newPrice.TaxPercentage2
                                    = newPrice.TaxPercentage3
                                    = "0";
                                listAdd.Add(newPrice);
                            }
                            else if (AppConfig.Pricing.OPTIONAL.Equals(tmpDynamicEditionValue))
                            {
                                // Update Price
                                var newPrice = optionPrice.Copy();
                                newPrice.ModelEditionOID = meOid;
                                listAdd.Add(newPrice);
                            }
                        }
                    }
                }
            }

            // add
            //save option s
            foreach (var item in listAdd)
            {
                var optionPrice = item;
                var option = optionPrice.Option;
                var checkOption = DBAccess.GetOption(option.OptionCode, option.GroupOID);
                if (checkOption == null)
                {
                    checkOption = DBAccess.CreateOptionModel(option);
                }
                option = checkOption;
                if (option.OID > 0 && optionPrice != null)
                {
                    optionPrice.OptionOID = option.OID;
                    //int temp2 = -1;
                    //temp2 = 
                    DBAccess.CreateOptionPrice(optionPrice);
                    //if (temp2 != -1)
                    //    countCreate++;
                }

            }

            // update
            foreach (var item in mapUpdate.Keys)
            {
                var key = item as string;
                var op = mapUpdate[key] as OptionPrice;
                var o = op.Option;
                //save option s
                if (o != null)
                {
                    int temp = DBAccess.UpdateOption(o);
                    if (temp != -1 && op != null)
                    {
                        //op.OptionOID = o.OID;
                        DBAccess.UpdateOptionPrice(op);
                        //if (temp2 != -1)
                        //    countUpdate++;
                    }

                }

            }

            var deleted = dt.GetChanges(DataRowState.Deleted);
            var ids1 = Utils.getIdDelete(deleted, FieldNames.OPTION_PRICELIST_OID + "s");
            if (!string.IsNullOrEmpty(ids1))
            {
                listDelete.Add(ids1);
            }
            var ids2 = string.Join(",", listDelete.ToArray());
            //var ids2 = Utils.getIdDelete(this.idDelete);
            //this.deleteItem(this.idDelete);
            DBAccess.DeleteByOid(FieldNames.Table.OPTION_PRICE, ids2);
            dt.AcceptChanges();
            // this.btnSearchOptionPricelist_Click(null, null);
        }

        int deleteItem(List<string> list)
        {
            int ret = -1;
            string id = "";
            id = Utils.getIdDelete(list);
            if (!id.Trim().Equals(""))
                ret = DBAccess.deleteOptionPrice(id);
            if (ret != -1)
                this.idDelete = new List<string>();
            return ret;
        }

        private void exportToCSVfile(string fileName, int makeOid, int modelYearOid, int langOid, DateTime pricelistDate)
        {
            var importMap = new Hashtable();
            var importSettings = AppConfig.GetExportSettings(AppConfig.Constants.AM_IMPORT_OPTION_PRICELIST);
            foreach (var item in importSettings.Keys)
            {
                var index = int.Parse(importSettings[item as string]);
                if (index < 0)
                {
                    continue;
                }
                importMap[item] = index;
            }
            // Default Order

            var listHeaderValid = new Hashtable();
            listHeaderValid.Add(ImportConfig.OptionPricelist.MAKE, FieldNames.MAKE);
            listHeaderValid.Add(ImportConfig.OptionPricelist.MODEL, FieldNames.MODEL);
            listHeaderValid.Add(ImportConfig.OptionPricelist.MODEL_EDITION, FieldNames.EDITION);
            listHeaderValid.Add(ImportConfig.OptionPricelist.OPTION_GROUP, FieldNames.GROUP);
            listHeaderValid.Add(ImportConfig.OptionPricelist.OPTION_BASE_NAME, FieldNames.BASE_NAME);
            listHeaderValid.Add(ImportConfig.OptionPricelist.OPTION_EXPLANATION, FieldNames.EXPLANATION);
            listHeaderValid.Add(ImportConfig.OptionPricelist.OPTION_CODE, FieldNames.CODE);
            listHeaderValid.Add(ImportConfig.OptionPricelist.OPTION_LANGUAGE, FieldNames.LANGUAGE);
            listHeaderValid.Add(ImportConfig.OptionPricelist.DEALER_NET0, "DealerNetPrice");
            listHeaderValid.Add(ImportConfig.OptionPricelist.DEALER_NET, "DealerNetPriceVAT");
            listHeaderValid.Add(ImportConfig.OptionPricelist.RETAIL_0, "RetailPrice");
            listHeaderValid.Add(ImportConfig.OptionPricelist.RETAIL, "RetailPriceVAT");
            listHeaderValid.Add(ImportConfig.OptionPricelist.TAX_1, "TaxAmount1");
            listHeaderValid.Add(ImportConfig.OptionPricelist.TAX_2, "TaxAmount2");
            listHeaderValid.Add(ImportConfig.OptionPricelist.TAX_3, "TaxAmount3");
            listHeaderValid.Add(ImportConfig.OptionPricelist.TAX_1P, "TaxPercentage1");
            listHeaderValid.Add(ImportConfig.OptionPricelist.TAX_2P, "TaxPercentage2");
            listHeaderValid.Add(ImportConfig.OptionPricelist.TAX_3P, "TaxPercentage3");
            listHeaderValid.Add(ImportConfig.OptionPricelist.DESCRIPTION, FieldNames.DESCRIPTION);
            var listHeaderOrder = new List<String>();
            var listIndexSort = importMap.Cast<DictionaryEntry>().OrderBy(entry => entry.Value).ToList();
            for (int i = 0; i < listIndexSort.Count; i++)
            {
                var item = listIndexSort[i];
                var key = item.Key;
                var value = listHeaderValid[key] as string;
                if (!string.IsNullOrEmpty(value))
                {
                    listHeaderOrder.Add(value);
                }
            }

            // Get settings
            var settings = AppConfig.GetExportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
            var format = settings[ImportConfig.Format.DECIMAL_FORMAT].GetDefault("0.#####");
            var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");
            var ds = DBAccess.GetOptionPricelist(makeOid, modelYearOid, langOid, pricelistDate);
            using (var writer = new StreamWriter(fileName, false, Encoding.UTF8))
            {
                // Write header
                for (int i = 0; i < listHeaderOrder.Count; i++)
                {
                    var columnName = listHeaderOrder[i];
                    var column = this.gvOptionPricelistNamed.Columns[columnName];
                    var displayName = column == null ? columnName : column.HeaderText;
                    writer.Write(displayName + separator);
                }

                foreach (var item in ds.Rows)
                {
                    var dataRow = item as DataRow;
                    writer.WriteLine();
                    for (int i = 0; i < listHeaderOrder.Count; i++)
                    {
                        var columnName = listHeaderOrder[i];
                        var dataValue = string.Empty;

                        if (dataRow[columnName].IsDecimal())
                        {
                            dataValue = ((decimal)dataRow[columnName]).ToString(format);
                        }
                        else
                        {
                            dataValue = dataRow[columnName].ToString();
                        }

                        //remove bad token
                        dataValue = dataValue.Replace(separator, string.Empty).Replace(Environment.NewLine, string.Empty);
                        writer.Write(dataValue + separator);
                    }

                }
            }
        }

        private void ImportOptionPricelist(List<string[]> listData)
        {
            var importMap = new Hashtable();



            if (listData.Count > 0)
            {
                var importSettings = AppConfig.GetImportSettings(AppConfig.Constants.AM_IMPORT_OPTION_PRICELIST);
                foreach (var item in importSettings.Keys)
                {
                    var index = int.Parse(importSettings[item as string]);
                    if (index >= listData[0].Length)
                    {
                        var msg = string.Format("Import settings Error: Key {0} index({1}) >= file columns({2})", item, index, listData[0].Length);
                        throw new Exception(msg);
                    }
                    importMap[item] = index;
                }
            }
            else
            {
                throw new Exception("No data to import!");
            }
            var settings = AppConfig.GetImportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
            var decimalSym = settings[ImportConfig.Format.DECIMAL_SYMBOL].GetDefault(".");
            var digitGroup = settings[ImportConfig.Format.DIGIT_GROUPING_SYMBOL].GetDefault(",");
            var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");
            var format = new NumberFormatInfo() { NumberDecimalSeparator = decimalSym };

            // Default value
            var makeOid = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID].ToString();
            var modelOid = this.cboModel.SelectedItem == null ? -1 : (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
            var modelYearOid = this.cboModelYear.SelectedItem == null ? -1 : (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
            var countryOid = this.cboCountry.SelectedItem == null ? -1 : (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
            //getvat
            GetVatFromCountry(countryOid.ToString());
            var currencyOid = this.cboCurrency.SelectedItem == null ? -1 : (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];
            var groupOid = this.cboOptionGroupPricelist.SelectedItem == null ? -1 : (int)(this.cboOptionGroupPricelist.SelectedItem as DataRowView)[FieldNames.OID];
            var langOid = this.cbLanguageOptionPricelist.SelectedItem == null ? -1 : (int)(this.cbLanguageOptionPricelist.SelectedItem as DataRowView)[FieldNames.OID];
            var pricelistDate = DateTime.Today;
            if (this.cboPricelistDateOptionPricelist.SelectedItem != null)
            {
                pricelistDate = (DateTime)this.cboPricelistDateOptionPricelist.SelectedItem;
            }
            var me = DBAccess.GetModelEdition(modelOid.ToString());
            var meNames = new object[0];
            if (me != null && me.Rows.Count > 0)
            {
                meNames = me.AsEnumerable().Select(rw => rw[FieldNames.NAME]).Distinct().ToArray();
            }
            var ds = this.gvOptionPricelistNamed.DataSource as DataTable;
            // read config
            var indexMake = (int)importMap[ImportConfig.OptionPricelist.MAKE];
            var indexModel = (int)importMap[ImportConfig.OptionPricelist.MODEL];
            var indexModelEdition = (int)importMap[ImportConfig.OptionPricelist.MODEL_EDITION];
            var indexOptionGroup = (int)importMap[ImportConfig.OptionPricelist.OPTION_GROUP];
            var indexOptionCode = (int)importMap[ImportConfig.OptionPricelist.OPTION_CODE];
            var indexOptionBaseName = (int)importMap[ImportConfig.OptionPricelist.OPTION_BASE_NAME];
            var indexOptionExplanation = (int)importMap[ImportConfig.OptionPricelist.OPTION_EXPLANATION];
            var indexOptionLanguage = (int)importMap[ImportConfig.OptionPricelist.OPTION_LANGUAGE];
            var indexTax1p = (int)importMap[ImportConfig.OptionPricelist.TAX_1P];
            var indexTax2p = (int)importMap[ImportConfig.OptionPricelist.TAX_2P];
            var indexTax3p = (int)importMap[ImportConfig.OptionPricelist.TAX_3P];
            var indexTax1 = (int)importMap[ImportConfig.OptionPricelist.TAX_1];
            var indexTax2 = (int)importMap[ImportConfig.OptionPricelist.TAX_2];
            var indexTax3 = (int)importMap[ImportConfig.OptionPricelist.TAX_3];
            var indexDealerNet0 = (int)importMap[ImportConfig.OptionPricelist.DEALER_NET0];
            var indexDealerNet = (int)importMap[ImportConfig.OptionPricelist.DEALER_NET];
            var indexRetail = (int)importMap[ImportConfig.OptionPricelist.RETAIL];
            var indexRetail0 = (int)importMap[ImportConfig.OptionPricelist.RETAIL_0];
            var indexDescription = (int)importMap[ImportConfig.OptionPricelist.DESCRIPTION];
            // cached database
            var mapModelEdition = new Hashtable();
            var mapOptionGroup = new Hashtable();
            var mapOption = new Hashtable();
            var mapOptionLanguage = new Hashtable();
            //var mapModelEditionIndex = new Hashtable();

            // remove header
            //listData.RemoveAt(0);
            var count = 0;
            var listOps = new List<OptionPrice>();
            //decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
            decimal vat = decimal.Parse(tax);
            foreach (var data in listData)
            {
                if (data.Length > 0)
                {
                    var makeName = indexMake < 0 ? string.Empty : data[indexMake];
                    var modelName = indexModel < 0 ? string.Empty : data[indexModel];
                    var modelEditionName = indexModelEdition < 0 ? string.Empty : data[indexModelEdition];
                    var optionCode = indexOptionCode < 0 ? string.Empty : data[indexOptionCode];
                    var optionGroup = indexOptionGroup < 0 ? string.Empty : data[indexOptionGroup];
                    if (optionGroup.Length >= 255)
                        optionGroup = optionGroup.Substring(0, 255);
                    // check model edition code
                    var mekey = string.Format("{0}|{1}|{2}", makeName, modelName, modelEditionName);
                    var modelEdition = mapModelEdition[mekey] as ModelEdition;
                    if (modelEdition == null)
                    {
                        modelEdition = DBAccess.GetModelEditionBy(makeName, modelName, modelEditionName);
                        if (modelEdition == null)
                        {
                            modelEdition = new ModelEdition();
                            modelEdition.OID = -1;
                        }
                        mapModelEdition[mekey] = modelEdition;
                    }
                    // check model edition exists
                    if ((-1).Equals(modelEdition.OID))
                    {
                        // ignore
                        continue;
                    }
                    // update OptionGroup
                    var ogkey = string.Format("{0}|{1}", optionCode, optionGroup);
                    var og = mapOptionGroup[ogkey] as OptionGroup;
                    if (og == null)
                    {
                        og = DBAccess.GetOptionGroup(modelEdition.Make.OID, optionGroup);
                        if (og == null)
                        {
                            // create new group
                            og = new OptionGroup();
                            og.MakeOID = modelEdition.Make.OID;
                            og.Name = optionGroup;
                            og = DBAccess.CreateOptionGroup(og);
                        }
                        mapOptionGroup[ogkey] = og;
                    }
                    // update OptionCode
                    var optKey = string.Format("{0}|{1}", og.OID, optionCode);
                    var option = mapOption[optKey] as Option;
                    if (option == null)
                    {
                        option = DBAccess.GetOption(optionCode, og.OID);
                        if (option == null)
                        {
                            option = new Option();

                            option.BaseName = indexOptionBaseName < 0 ? string.Empty : data[indexOptionBaseName].Length > 255 ? data[indexOptionBaseName].Substring(0, 255) : data[indexOptionBaseName];

                            option.Explanation = indexOptionExplanation < 0 ? string.Empty : data[indexOptionExplanation].Length > 255 ? data[indexOptionExplanation].Substring(0, 255) : data[indexOptionExplanation];
                            option.GroupOID = og.OID;
                            option.OptionCode = optionCode;
                            option = DBAccess.CreateOptionModel(option);
                        }
                        mapOption[optKey] = option;
                    }

                    // update OptionName
                    var langKey = string.Format("{0}|{1}", optionCode, langOid);
                    var optName = mapOptionLanguage[langKey] as OptionName;
                    if (optName == null && indexOptionLanguage > -1)
                    {
                        optName = DBAccess.GetOptionName(option.OID, langOid);
                        if (optName == null)
                        {
                            // create optionName
                            optName = new OptionName();
                            optName.LanguageOID = langOid;
                            optName.OptionOID = option.OID;
                            optName.Name = data[indexOptionLanguage];
                            optName = DBAccess.CreateOptionName(optName);
                        }
                        mapOptionLanguage[langKey] = optName;
                    }

                    var optionPrice = new OptionPrice();
                    optionPrice.ModelEditionOID = modelEdition.OID;
                    optionPrice.ModelYearOID = modelYearOid;
                    optionPrice.CountryOID = countryOid;
                    optionPrice.CurrencyOID = currencyOid;
                    optionPrice.PriceListDate = pricelistDate;

                    optionPrice.DealerNetPrice = indexDealerNet0 < 0 ? string.Empty : data[indexDealerNet0];
                    if (string.IsNullOrEmpty(optionPrice.DealerNetPrice) || indexDealerNet0 < 0)
                    {
                        optionPrice.DealerNetPriceVAT = indexDealerNet < 0 ? string.Empty : data[indexDealerNet];
                        var grossStr = optionPrice.DealerNetPrice;
                        decimal gross = decimal.Parse(grossStr, format);

                        decimal net = Utils.NetCalculate(gross, vat);
                        optionPrice.DealerNetPrice = Utils.CommaToDot(net.ToString());
                    }
                    else
                    {
                        var netStr = optionPrice.DealerNetPrice;
                        //netStr = Utils.CommaToDot(netStr);
                        decimal net = decimal.Parse(netStr, format);


                        decimal gros = Utils.GrosCalculate(net, vat);
                        optionPrice.DealerNetPriceVAT = Utils.CommaToDot(gros.ToString());
                    }

                    optionPrice.RetailPrice = indexRetail0 < 0 ? string.Empty : data[indexRetail0];
                    if (string.IsNullOrEmpty(optionPrice.RetailPrice) || indexRetail0 <0)
                    {
                        optionPrice.RetailPriceVAT = indexRetail0 < 0 ? string.Empty : data[indexRetail];
                        var grossStr = optionPrice.RetailPrice;
                        decimal gross = decimal.Parse(grossStr, format);

                        decimal net = Utils.NetCalculate(gross, vat);
                        optionPrice.RetailPrice = Utils.CommaToDot(net.ToString());
                    }
                    else
                    {
                        var netStr = optionPrice.RetailPrice;
                        // netStr = Utils.CommaToDot(netStr);
                        decimal net = decimal.Parse(netStr, format);

                        decimal gros = Utils.GrosCalculate(net, vat);
                        optionPrice.RetailPriceVAT = Utils.CommaToDot(gros.ToString());
                    }

                   // optionPrice.RetailPriceVAT = indexRetail0 < 0 ? string.Empty : data[indexRetail];
                    optionPrice.TaxAmount1 = indexTax1 < 0 ? string.Empty : data[indexTax1];
                    optionPrice.TaxAmount2 = indexTax2 < 0 ? string.Empty : data[indexTax2];
                    optionPrice.TaxAmount3 = indexTax3 < 0 ? string.Empty : data[indexTax3];
                    optionPrice.TaxPercentage1 = indexTax1p < 0 ? string.Empty : data[indexTax1p];
                    optionPrice.TaxPercentage2 = indexTax2p < 0 ? string.Empty : data[indexTax2p];
                    optionPrice.TaxPercentage3 = indexTax3p < 0 ? string.Empty : data[indexTax3p];
                    optionPrice.Option = option;
                    optionPrice.OptionOID = option.OID;
                    optionPrice.Description = indexDescription < 0 ? string.Empty : data[indexDescription];
                    listOps.Add(optionPrice);
                    count++;
                    if (listOps.Count > 300)
                    {
                        DBAccess.CreateOptionPrice(listOps);
                        listOps.Clear();
                    }
                }
            }

            if (listOps.Count > 0)
            {
                DBAccess.CreateOptionPrice(listOps);
                listOps.Clear();
            }
        }


        private void ImportFromCSV(string filepath)
        {
            var settings = AppConfig.GetImportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
            var format = settings[ImportConfig.Format.DECIMAL_FORMAT].GetDefault("0.#####");
            var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");

            List<string[]> listParse = DBAccess.parseCSV(filepath, separator);

            listParse.RemoveAt(0);
            foreach (string[] row in listParse)
            {
                if (row.Length > 0)
                {
                    Option o = new Option();
                    o.OID = -1;
                    o.OptionCode = row[4];
                    o.GroupOID = DBAccess.getGroupOidFromName(row[3]);// group must existed in db
                    o.BaseName = row[5];
                    o.Explanation = row[6];

                    o = DBAccess.CreateOptionModel(o);

                    OptionName on = new OptionName();
                    on.OID = -1;
                    on.LanguageOID = this.cbLanguageOptionPricelist.SelectedIndex == -1 ? -1 : (int)this.cbLanguageOptionPricelist.SelectedValue;
                    on.OptionOID = o.OID;
                    on.Name = row[17];

                    DBAccess.InsertOptionName(on);


                    OptionPrice op = new OptionPrice();
                    op.CountryOID = this.cboCountry.SelectedIndex == -1 ? -1 : (int)this.cboCountry.SelectedValue;
                    op.CurrencyOID = this.cboCurrency.SelectedIndex == -1 ? -1 : (int)this.cboCurrency.SelectedValue;
                    op.ModelYearOID = this.cboModelYear.SelectedIndex == -1 ? -1 : (int)this.cboModelYear.SelectedValue;
                    op.ModelEditionOID = DBAccess.GetModelEditionOidByName(row[17]);
                    op.OptionOID = o.OID;
                    op.PriceListDate = DateTime.Today;
                    op.TaxAmount1 = row[11];
                    op.TaxAmount2 = row[12];
                    op.TaxAmount3 = row[13];

                    op.TaxPercentage1 = row[14];
                    op.TaxPercentage2 = row[15];
                    op.TaxPercentage3 = row[16];

                    op.DealerNetPrice = row[7];
                    op.DealerNetPriceVAT = row[8];
                    op.RetailPrice = row[9];
                    op.RetailPriceVAT = row[11];

                    int i = -1;
                    i = DBAccess.CreateOptionPrice(op);

                }
            }

            this.btnSearchOptionPricelist_Click(null, null);
        }

        //-------------------------------------control event-------------------------------

        private void cboMakeOptionPriceList_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //if (this.gvOptionPricelistNamed.DataSource != null)
                //{
                //    gvOptionPricelistNamed.Rows.Clear();
                //    gvOptionPricelistNamed.Refresh();
                //}
                var dtRow = this.cboMakes.SelectedItem as DataRowView;
                var makeOid = dtRow[FieldNames.OID].ToString();
                // Model
                this.cboModel.DataSource = DBAccess.GetModels(makeOid);
                if (((DataTable)this.cboModel.DataSource).Rows.Count != 0)
                {
                    this.cboModel.ValueMember = FieldNames.OID;
                    this.cboModel.DisplayMember = FieldNames.NAME;
                }
                // Model year
                this.cboModelYear.DataSource = DBAccess.GetModelYear(makeOid);
                this.cboModelYear.ValueMember = FieldNames.OID;
                this.cboModelYear.DisplayMember = FieldNames.NAME;
                // Option group
                this.cboOptionGroupPricelist.DataSource = DBAccess.GetOptionGroup(makeOid);
                this.cboOptionGroupPricelist.ValueMember = FieldNames.OID;
                this.cboOptionGroupPricelist.DisplayMember = FieldNames.NAME;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnSaveOptionPricelist_Click(object sender, EventArgs e)
        {
            //DialogResult result1 = MessageBox.Show("Are you sure to save the change?", "Confirm Message", MessageBoxButtons.YesNo);
            //if (result1 == DialogResult.Yes)
            //{
            try
            {
                this.SaveGrid(this.gvOptionPricelistNamed.DataSource as DataTable, false);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Please check input: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            //}
        }

        private void btnSearchOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var makeOid = cboMakes.SelectedIndex == -1 ? "" : cboMakes.SelectedValue.ToString();
                var modelOid = cboModel.SelectedIndex == -1 ? "" : cboModel.SelectedValue.ToString();
                var modelyearOid = cboModelYear.SelectedIndex == -1 ? "" : cboModelYear.SelectedValue.ToString();
                var groupOid = cboOptionGroupPricelist.SelectedIndex == -1 ? "" : this.cboOptionGroupPricelist.SelectedValue.ToString();
                var countryOid = cboCountry.SelectedIndex == -1 ? "" : cboCountry.SelectedValue.ToString();
                var currencyOid = cboCurrency.SelectedIndex == -1 ? "" : cboCurrency.SelectedValue.ToString();
                var pricelistDate = cboPricelistDateOptionPricelist.SelectedIndex == -1 ? "" : ((DateTime)cboPricelistDateOptionPricelist.SelectedValue).ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT);
                var me = DBAccess.GetModelEdition(modelOid);
                var ds = DBAccess.GetOptionPricelist(modelOid, groupOid, modelyearOid, countryOid, currencyOid, pricelistDate, makeOid);
                this.GetVatFromCountry(countryOid);

                // add dynamic edition columns
                if (this.gvOptionPricelistNamed.DataSource != null)
                {
                    (this.gvOptionPricelistNamed.DataSource as DataTable).Clear();
                }
                var comboBox = this.gvOptionPricelistNamed.Columns[FieldNames.OPTION_GROUP_PRICELIST] as DataGridViewComboBoxColumn;
                comboBox.DataSource = DBAccess.GetOptionGroup(makeOid);
                comboBox.ValueMember = FieldNames.OID;
                comboBox.DisplayMember = FieldNames.NAME;

                //var comboBox2 = this.gvOptionPricelistNamed.Columns[FieldNames.OID_MODEL_EDITION] as DataGridViewComboBoxColumn;
                //comboBox2.DataSource = me;
                //comboBox2.ValueMember = FieldNames.OID;
                //comboBox2.DisplayMember = FieldNames.NAME;


                // add dynamic columns
                var colen = me.Rows.Count;
                var modelEditionNames = me.AsEnumerable().Select(row => row[FieldNames.NAME]).Distinct().ToArray();

                foreach (var name in modelEditionNames)
                {
                    ds.Columns.Add(name as String, typeof(String));
                }

                var mapDelete = new Hashtable();

                foreach (DataRow row in ds.Rows)
                {
                    var tmpOptionOid = (int)row["OptionOid"];
                    var tmpModelEditionOids = row["ModelEditionOID"] as string;
                    var tmpOptionPricelistOidTokens = row["OptionPricelistOid"] as string;
                    var tmpOptionPricelistOids = row["OptionPricelistOids"] as string;
                    var tmpModelYearOid = (int)row["ModelYearOID"];
                    var tmpCountryOid = (int)row["CountryOID"];
                    var tmpCurrencyOid = (int)row["CurrencyOID"];
                    var tmpPricelistDate = (DateTime)row["PriceListDate"];
                    var meOids = Regex.Split(tmpModelEditionOids, ",");
                    // Calculate Price ?longpro
                    var tmpDealerNetPrice = (row[FieldNames.PRICE_DEALER_NET] != null && !string.IsNullOrEmpty(row[FieldNames.PRICE_DEALER_NET].ToString())) ? (decimal)row[FieldNames.PRICE_DEALER_NET] : 0;
                    var tmpDealerNetPriceVAT = (row[FieldNames.PRICE_DEALER_NET_VAT] != null && !string.IsNullOrEmpty(row[FieldNames.PRICE_DEALER_NET_VAT].ToString())) ? (decimal)row[FieldNames.PRICE_DEALER_NET_VAT] : 0;
                    var tmpRetailPrice = (row[FieldNames.PRICE_RETAIL] != null && !string.IsNullOrEmpty(row[FieldNames.PRICE_RETAIL].ToString())) ? (decimal)row[FieldNames.PRICE_RETAIL] : 0;
                    var tmpRetailPriceVAT = (row[FieldNames.PRICE_RETAIL_VAT] != null && !string.IsNullOrEmpty(row[FieldNames.PRICE_RETAIL_VAT].ToString())) ? (decimal)row[FieldNames.PRICE_RETAIL_VAT] : 0;

                    var tmpTotal = tmpDealerNetPrice + tmpDealerNetPriceVAT + tmpRetailPrice + tmpRetailPriceVAT;
                    var tmpOptionFilter = string.Format("OptionOid = '{0}' AND ModelEditionOID <> '{1}'", tmpOptionOid, tmpModelEditionOids);
                    var optionPrice = ds.Select(tmpOptionFilter);
                    //var meWithThisOption = ds.AsEnumerable().Where(r => (int)r[FieldNames.OID_OPTION] == tmpOptionOid).ToArray();

                    if (tmpTotal == 0M)
                    {
                        // Delete
                        //row.Delete();
                        foreach (var item in optionPrice)
                        {
                            item["OptionPricelistOid"] += "," + tmpOptionPricelistOidTokens;
                            item["OptionPricelistOids"] += "," + tmpOptionPricelistOids;
                        }
                        // Delete duplicate view ?
                        if (optionPrice.Length > 0)
                        {
                            mapDelete[tmpOptionOid + "|" + tmpModelEditionOids] = row;
                        }
                        else
                        {
                            foreach (var name in modelEditionNames)
                            {
                                var filter = string.Format("Name = '{0}'", name);
                                var meOid = me.Select(filter)[0][FieldNames.OID].ToString();
                                var meName = name as String;
                                if (meOids.Contains(meOid))
                                {
                                    row[meName] = AppConfig.Pricing.STANDARD;
                                }
                                else
                                {
                                    row[meName] = AppConfig.Pricing.UNAVAILABLE;
                                }
                            }
                        }
                        continue;
                    }


                    foreach (var name in modelEditionNames)
                    {

                        var filter = string.Format("Name = '{0}'", name);
                        var meOid = me.Select(filter)[0][FieldNames.OID].ToString();
                        var meName = name as String;

                        if (meOids.Contains(meOid))
                        {
                            row[meName] = AppConfig.Pricing.OPTIONAL;

                        }
                        else
                        {
                            // check exists or standard
                            var meIsStandard = false;

                            foreach (var item in optionPrice)
                            {

                                tmpModelEditionOids = item["ModelEditionOID"] as string;
                                var tokens2 = Regex.Split(tmpModelEditionOids, ",");
                                if (tokens2.Contains(meOid))
                                {
                                    meIsStandard = true;
                                    break;
                                }
                            }
                            if (meIsStandard)
                            {
                                row[meName] = AppConfig.Pricing.STANDARD;
                            }
                            else
                            {
                                row[meName] = AppConfig.Pricing.UNAVAILABLE;
                            }
                        }

                    }
                }

                foreach (var item in mapDelete.Values)
                {
                    var dataRow = item as DataRow;
                    dataRow.Delete();
                }
                mapDelete.Clear();
                // get model edition
                ds.AcceptChanges();
                //this.loadComboColumn();
                this.gvOptionPricelistNamed.DataSource = ds;

                // Reorder
                this.Reorder(modelEditionNames);

                this.gvOptionPricelistNamed.Columns["ModelEditionOID"].Tag = me;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


        }

        private void Reorder(object[] modelEditionNames)
        {
            var tmpIndex = 0;
            this.gvOptionPricelistNamed.Columns["OptionOid"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["OptionGroupPriceList"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["Code"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["BaseName"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["Explanation"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["ModelEditionOID"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["optionpricelistOid"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["PriceListDate"].DisplayIndex = tmpIndex++;

            var editionSource = new String[] { AppConfig.Pricing.STANDARD, AppConfig.Pricing.OPTIONAL, AppConfig.Pricing.UNAVAILABLE };

            foreach (var name in modelEditionNames)
            {
                var meName = name as String;
                var colIndex = this.gvOptionPricelistNamed.Columns[meName].Index;
                var column = this.gvOptionPricelistNamed.Columns[meName];
                var newCol = new DataGridViewComboBoxColumn();
                newCol.Name = meName;
                newCol.DataPropertyName = meName;
                //newCol.DataSource = editionSource;
                newCol.DisplayIndex = tmpIndex++;
                newCol.ReadOnly = false;

                this.gvOptionPricelistNamed.Columns.RemoveAt(colIndex);
                this.gvOptionPricelistNamed.Columns.Insert(colIndex, newCol);
                // set data source os-
                var cbo = this.gvOptionPricelistNamed.Columns[meName] as DataGridViewComboBoxColumn;
                cbo.DataSource = editionSource;
            }

            this.gvOptionPricelistNamed.Columns["DealerNetPrice"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["DealerNetPriceVAT"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["RetailPrice"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["RetailPriceVAT"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["TaxAmount1"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["TaxAmount2"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["TaxAmount3"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["TaxPercentage1"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["TaxPercentage2"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["TaxPercentage3"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["CountryOID"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["CurrencyOID"].DisplayIndex = tmpIndex++;
            this.gvOptionPricelistNamed.Columns["ModelYearOID"].DisplayIndex = tmpIndex++;
        }

        private void ScrollToBottom()
        {
            var lastRow = this.gvOptionPricelistNamed.Rows.Count - 1;
            this.gvOptionPricelistNamed.FirstDisplayedScrollingRowIndex = lastRow;
        }

        private void btnNewOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var makeOid = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID].ToString();

                var modelOid = this.cboModel.SelectedItem == null ? -1 : (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
                var modelYearOid = this.cboModelYear.SelectedItem == null ? -1 : (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
                var countryOid = this.cboCountry.SelectedItem == null ? -1 : (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
                var currencyOid = this.cboCurrency.SelectedItem == null ? -1 : (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];
                var groupOid = this.cboOptionGroupPricelist.SelectedItem == null ? -1 : (int)(this.cboOptionGroupPricelist.SelectedItem as DataRowView)[FieldNames.OID];
                var pricelistDate = cboPricelistDateOptionPricelist.SelectedIndex == -1 ? DateTime.Today : ((DateTime)cboPricelistDateOptionPricelist.SelectedValue);

                var dt = this.gvOptionPricelistNamed.DataSource as DataTable;
                var dataRow = dt.NewRow();

                //dataRow[FieldNames.OID] = -1;
                dataRow[FieldNames.OID_GROUP] = (this.cboOptionGroupPricelist.SelectedValue != null) ?
                     this.cboOptionGroupPricelist.SelectedValue : DBNull.Value;
                dataRow[FieldNames.OPTION_CODE] = "optioncode";
                dataRow[FieldNames.BASE_NAME] = "BaseName";
                dataRow[FieldNames.EXPLANATION] = "explanation";
                dataRow[FieldNames.PRICE_LIST_DATE] = pricelistDate;

                dataRow[FieldNames.OID_COUNTRY] = countryOid;
                dataRow[FieldNames.OID_CURRENCY] = currencyOid;
                dataRow[FieldNames.OID_MODEL_YEAR] = modelYearOid;
                dataRow[FieldNames.OID_MODEL_EDITION] = DBNull.Value;

                dt.Rows.Add(dataRow);
                this.ScrollToBottom();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        //private void gvOptionPricelistNamed_DataError(object sender, DataGridViewDataErrorEventArgs e)
        //{
        //    //MessageBox.Show("Error happened " + e.Context.ToString());
        //}

        private void btnDeleteSelectedOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                foreach (DataGridViewRow item in this.gvOptionPricelistNamed.SelectedRows)
                {
                    gvOptionPricelistNamed.Rows.RemoveAt(item.Index);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void panel1_Leave(object sender, EventArgs e)
        {

        }

        private void btnDuplicateOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.gvOptionPricelistNamed.Rows.Count > 0)
                {
                    // Get duplicated data
                    //var makeOid = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID].ToString();
                    //var modelOid = (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
                    var modelYearOid = (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
                    var countryOid = (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
                    var currencyOid = (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];
                    var groupOid = (int)(this.cboOptionGroupPricelist.SelectedItem as DataRowView)[FieldNames.OID];
                    var pricelistDate = cboPricelistDateOptionPricelist.SelectedIndex == -1 ? DateTime.Today : ((DateTime)cboPricelistDateOptionPricelist.SelectedValue);

                    //var make = this.cboMakes.DataSource as DataTable;
                    var country = this.cboCountry.DataSource as DataTable;
                    var currency = this.cboCurrency.DataSource as DataTable;
                    var modelYear = this.cboModelYear.DataSource as DataTable;
                    var duplicateForm = new FrmDuplicatePricelist();
                    duplicateForm.LoadDataComponent(country.Copy(), currency.Copy(), modelYear.Copy());
                    duplicateForm.LoadDataDefault(countryOid, currencyOid, modelYearOid, pricelistDate);
                    duplicateForm.FormClosed += new FormClosedEventHandler(duplicateForm_FormClosed);
                    duplicateForm.ShowDialog();
                    //this.reloadDataGridView(); 
                }
                else
                {
                    MessageBox.Show("No data to duplicate");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }



        private void duplicateForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            try
            {
                var form = sender as Form;
                if (form.DialogResult == DialogResult.OK)
                {
                    var data = form.Tag as Hashtable;
                    // parse
                    var countryOid = (int)data[FieldNames.OID_COUNTRY];
                    var currencyOid = (int)data[FieldNames.OID_CURRENCY];
                    var modelYearOid = (int)data[FieldNames.OID_MODEL_YEAR];
                    var pricelistDate = (DateTime)data[FieldNames.PRICE_LIST_DATE];
                    var dt = this.gvOptionPricelistNamed.DataSource as DataTable;
                    var duplicatedDs = dt.Clone();
                    foreach (var item in this.gvOptionPricelistNamed.Rows)
                    {
                        var dataRow = item as DataGridViewRow;
                        var optionOid = (int)dataRow.Cells[FieldNames.OPTION_OID].Value;
                        var groupOid = (int)dataRow.Cells[FieldNames.OPTION_GROUP_PRICELIST].Value;
                        var optionCode = dataRow.Cells[FieldNames.CODE].Value as string;
                        var baseName = dataRow.Cells[FieldNames.BASE_NAME].Value as string;
                        var explaination = dataRow.Cells[FieldNames.EXPLANATION].Value as string;
                        var newRow = duplicatedDs.NewRow();

                        //dataRow[FieldNames.OID] = -1;
                        newRow[FieldNames.OID_GROUP] = groupOid;
                        newRow[FieldNames.OPTION_CODE] = optionCode;
                        newRow[FieldNames.BASE_NAME] = baseName;
                        newRow[FieldNames.EXPLANATION] = explaination;
                        newRow[FieldNames.PRICE_LIST_DATE] = pricelistDate;

                        newRow[FieldNames.OID_OPTION] = optionOid;
                        newRow[FieldNames.OID_COUNTRY] = countryOid;
                        newRow[FieldNames.OID_CURRENCY] = currencyOid;
                        newRow[FieldNames.OID_MODEL_YEAR] = modelYearOid;
                        newRow[FieldNames.OID_MODEL_EDITION] = DBNull.Value;
                        // duplicate price

                        // TaxPercentage
                        newRow["TaxPercentage1"] = dataRow.Cells["TaxPercentage1"].Value;
                        newRow["TaxPercentage2"] = dataRow.Cells["TaxPercentage2"].Value;
                        newRow["TaxPercentage3"] = dataRow.Cells["TaxPercentage3"].Value;
                        // TaxAmount
                        newRow["TaxAmount1"] = dataRow.Cells["TaxAmount1"].Value;
                        newRow["TaxAmount2"] = dataRow.Cells["TaxAmount2"].Value;
                        newRow["TaxAmount3"] = dataRow.Cells["TaxAmount3"].Value;
                        // Dealer
                        newRow["DealerNetPrice"] = dataRow.Cells["DealerNetPrice"].Value;
                        newRow["DealerNetPriceVAT"] = dataRow.Cells["DealerNetPriceVAT"].Value;
                        // Retail
                        newRow["RetailPrice"] = dataRow.Cells["RetailPrice"].Value;
                        newRow["RetailPriceVAT"] = dataRow.Cells["RetailPriceVAT"].Value;

                        // price label
                        var me = this.gvOptionPricelistNamed.Columns["ModelEditionOID"].Tag as DataTable;
                        if (me != null && me.Rows.Count > 0)
                        {
                            var meNames = me.AsEnumerable().Select(rw => rw[FieldNames.NAME]).Distinct().ToArray();
                            foreach (var name in meNames)
                            {
                                var meName = name as string;
                                newRow[meName] = dataRow.Cells[meName].Value;
                            }
                        }
                        // add new
                        duplicatedDs.Rows.Add(newRow);
                    }

                    // update view
                    dt.Clear();
                    this.gvOptionPricelistNamed.DataSource = duplicatedDs;
                    this.ScrollToBottom();
                    // Update conditions
                    this.cboModelYear.SelectedValue = modelYearOid;
                    this.cboCountry.SelectedValue = countryOid;
                    this.cboCurrency.SelectedValue = currencyOid;
                    //this.cboOptionGroupPricelist.SelectedValue = groupOid;
                    var dateOnly = this.cboPricelistDateOptionPricelist.DataSource as List<object>;
                    if (dateOnly != null && !dateOnly.Contains(pricelistDate))
                    {
                        dateOnly.Add(pricelistDate);
                        dateOnly.Sort();
                        dateOnly.Reverse();
                        this.cboPricelistDateOptionPricelist.DataSource = null;
                        this.cboPricelistDateOptionPricelist.DataSource = dateOnly;
                    }

                    var selectIndex = dateOnly.IndexOf(pricelistDate);
                    if (selectIndex != -1)
                    {
                        this.cboPricelistDateOptionPricelist.SelectedIndex = selectIndex;
                    }

                    // Save to dabase
                    //DBAccess.CreateOptionPrice(op);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnDuplicateSelectedOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.gvOptionPricelistNamed.SelectedRows.Count > 0)
                {
                    DataTable dt = (DataTable)this.gvOptionPricelistNamed.DataSource;
                    int index = gvOptionPricelistNamed.SelectedRows[0].Index;
                    var desRow = dt.NewRow();
                    var sourceRow = dt.Rows[index];
                    desRow.ItemArray = sourceRow.ItemArray.Clone() as object[];
                    desRow[0] = "-1";
                    dt.Rows.Add(desRow);
                    this.ScrollToBottom();
                }
                else
                {
                    MessageBox.Show("Please select a full row to duplicate");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnExportOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var pricelistDate = DateTime.Today;
                if (this.cboPricelistDateOptionPricelist.SelectedItem != null)
                {
                    pricelistDate = (DateTime)this.cboPricelistDateOptionPricelist.SelectedItem;
                }
                // Default value
                var makeOid = (int)(this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID];
                var makeName = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.NAME] as string;
                //var modelOid = (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
                var modelYearOid = (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
                var modelYearName = (this.cboModelYear.SelectedItem as DataRowView)[FieldNames.NAME] as string;
                //var countryOid = (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
                //var currencyOid = (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];
                //var groupOid = (int)(this.cboOptionGroupPricelist.SelectedItem as DataRowView)[FieldNames.OID];
                var langOid = (int)(this.cbLanguageOptionPricelist.SelectedItem as DataRowView)[FieldNames.OID];
                var langCode = (this.cbLanguageOptionPricelist.SelectedItem as DataRowView)[FieldNames.CODE] as string;

                SaveFileDialog saveFileDialogCSV = new SaveFileDialog();
                saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
                saveFileDialogCSV.FilterIndex = 1;
                saveFileDialogCSV.RestoreDirectory = true;
                saveFileDialogCSV.FileName = string.Format("optionPricelist.{0}.{1}.{2}.{3}.csv", makeName, modelYearName, langCode, pricelistDate.ToString("yyyy-MM-dd"));
                saveFileDialogCSV.InitialDirectory = Application.ExecutablePath;

                if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
                {
                    var fileName = saveFileDialogCSV.FileName;
                    exportToCSVfile(fileName, makeOid, modelYearOid, langOid, pricelistDate);
                    MessageBox.Show("Export successful.\nFileName: " + fileName, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Eror", MessageBoxButtons.OK,
                MessageBoxIcon.Error);

            }
        }

        private void btnImportOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var settings = AppConfig.GetImportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
                var format = settings[ImportConfig.Format.DECIMAL_FORMAT].GetDefault("0.#####");
                var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");


                OpenFileDialog openFileDialogCSV = new OpenFileDialog();


                openFileDialogCSV.InitialDirectory = Application.ExecutablePath;
                openFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
                openFileDialogCSV.FilterIndex = 1;
                openFileDialogCSV.RestoreDirectory = true;

                if (openFileDialogCSV.ShowDialog() == DialogResult.OK)
                {
                    List<string[]> listParse = DBAccess.parseCSV(openFileDialogCSV.FileName, separator);
                    this.ImportOptionPricelist(listParse);
                    MessageBox.Show("Import complete!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "ERROR!", MessageBoxButtons.OK,
                   MessageBoxIcon.Error);
            }
        }

        private void gvOptionPricelistNamed_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            //var gv = sender as DataGridView;
            //if (null != gv)
            //{

            //    var me = gv.Columns["ModelEditionOID"].Tag as DataTable;
            //    var plist = gv.DataSource as DataTable;


            //    foreach (DataGridViewRow r in gv.Rows)
            //    {
            //        gv.Rows[r.Index].HeaderCell.Value = (r.Index + 1).ToString();
            //    }
            //}
        }

        private void gvOptionPricelistNamed_DataSourceChanged(object sender, EventArgs e)
        {

        }

        private void gvOptionPricelistNamed_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            var gv = sender as DataGridView;
            if (null != gv)
            {
                for (int index = e.RowIndex; index <= e.RowIndex + e.RowCount - 1; index++)
                {
                    DataGridViewRow row = gv.Rows[index];

                    // Do something with the added row here
                    // Raise a custom RowAdded event if you want that passes individual rows.
                    gv.Rows[row.Index].HeaderCell.Value = (row.Index + 1).ToString();
                }
            }
        }

        private void gvOptionPricelistNamed_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            var retailNetIndex = this.gvOptionPricelistNamed.Columns[FieldNames.RETAIL_PRICE].Index;
            var retailGrosIndex = gvOptionPricelistNamed.Columns[FieldNames.RETAIL_PRICE_VAT].Index;

            var dealerNetIndex = gvOptionPricelistNamed.Columns[FieldNames.DEALER_NET_PRICE].Index;
            var dealerGrosIndex = gvOptionPricelistNamed.Columns[FieldNames.DEALER_NET_PRICE_VAT].Index;
            DataGridViewRow r = gvOptionPricelistNamed.Rows[e.RowIndex];
            var format = new NumberFormatInfo() { NumberDecimalSeparator = ".", NumberGroupSeparator = ",", PercentDecimalDigits = 2 };

            if (retailNetIndex == e.ColumnIndex) // change net =>update gros
            {
                if (r != null)
                {
                    string netStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(netStr))
                    {
                        MessageBox.Show("Invalid Format Value");
                    }
                    else
                    {
                        netStr = Utils.CommaToDot(netStr);
                        decimal net = decimal.Parse(netStr, format);
                        decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                        decimal gros = Utils.GrosCalculate(net, vat);
                        gvOptionPricelistNamed.Rows[e.RowIndex].Cells[retailGrosIndex].Value = gros;//.ToString(CultureInfo.InvariantCulture);
                    }
                }
            }

            if (dealerNetIndex == e.ColumnIndex) // change net =>update gros
            {
                if (r != null)
                {
                    string netStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(netStr))
                    {
                        MessageBox.Show("Invalid Format Value");
                    }
                    else
                    {
                        netStr = Utils.CommaToDot(netStr);

                        decimal net = decimal.Parse(netStr, format);
                        decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                        decimal gros = Utils.GrosCalculate(net, vat);
                        gvOptionPricelistNamed.Rows[e.RowIndex].Cells[dealerGrosIndex].Value = gros;//.ToString(CultureInfo.InvariantCulture);
                    }
                }
            }

            if (retailGrosIndex == e.ColumnIndex) // change gros =>update net
            {
                if (r != null)
                {
                    string grosStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(grosStr))
                    {
                        MessageBox.Show("Invalid Format Value");
                    }
                    else
                    {
                        grosStr = Utils.CommaToDot(grosStr);

                        decimal gros = decimal.Parse(grosStr, format);
                        decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                        decimal net = Utils.NetCalculate(gros, vat);
                        gvOptionPricelistNamed.Rows[e.RowIndex].Cells[retailNetIndex].Value = net;//.ToString(CultureInfo.InvariantCulture);
                    }
                }
            }
            if (dealerGrosIndex == e.ColumnIndex) // change gros =>update net
            {
                if (r != null)
                {
                    string grosStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(grosStr))
                    {
                        MessageBox.Show("Invalid Format Value");
                    }
                    else
                    {
                        grosStr = Utils.CommaToDot(grosStr);
                        decimal gros = decimal.Parse(grosStr, format);
                        decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                        decimal net = Utils.NetCalculate(gros, vat);
                        gvOptionPricelistNamed.Rows[e.RowIndex].Cells[dealerNetIndex].Value = net;//.ToString(CultureInfo.InvariantCulture);
                    }
                }
            }
        }

        private void cboMakes_SelectedValueChanged(object sender, EventArgs e)
        {
        }

        private void cboMakes_SelectionChangeCommitted(object sender, EventArgs e)
        {
            this.btnSearchOptionPricelist_Click(sender, e);
        }

        private void gvOptionPricelistNamed_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

        private void gvOptionPricelistNamed_DataError_1(object sender, DataGridViewDataErrorEventArgs e)
        {

        }


    }
}
